class CreateDerbymatic < ActiveRecord::Migration

  def self.up
    #-----------------------------------
    create_table :interleague_organizations do |t|
      t.column :name,               :string,   :default => "", :null => false
      t.column :abbreviation,       :string,   :limit => 10
      t.column :location_id,        :integer # foreign key => locations
      t.column :logo_url,           :string
      t.column :thumb_url,          :string
      t.column :website_url,        :string

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :interleague_memberships do |t|
      t.column :interleague_organization_id, :integer # foreign key => interleague_organizations
      t.column :league_id,          :integer # foreign key => leagues
      t.column :joined_on,          :datetime
      t.column :retired_on,         :datetime

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :leagues do |t|
      t.column :name,               :string,   :default => "", :null => false
      t.column :abbreviation,       :string,   :limit => 10
      t.column :location_id,        :integer # foreign key => locations
      t.column :founded_on,         :datetime
      t.column :logo_url,           :string
      t.column :thumb_url,          :string
      t.column :website_url,        :string

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :teams do |t|
      t.column :name,               :string,   :default => "", :null => false
      t.column :abbreviation,       :string,   :limit => 10
      t.column :league_id,          :integer # foreign key => leagues
      t.column :founded_on,         :datetime
      t.column :logo_url,           :string
      t.column :thumb_url,          :string
      t.column :website_url,        :string

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    #-----------------------------------

    create_table :skaters do |t|
      t.column :name,               :string,                :default => "",  :null => false
      t.column :person_id,          :integer # foreign key => people
      t.column :officiating_number, :string
      t.column :jersey_number,      :string
      t.column :picture_url,        :string
      t.column :thumb_url,          :string
      t.column :website_url,        :string

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :skater_teams do |t|
      t.column :skater_id,          :integer # foreign key => skaters
      t.column :team_id,            :integer # foreign key => teams
      t.column :is_captain,         :string,   :limit => 1, :default => "N", :null => false
      t.column :joined_on,          :datetime
      t.column :retired_on,         :datetime

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :team_captains do |t|
      t.column :skater_team_id,     :integer # foreign key => teams
      t.column :started_on,         :datetime
      t.column :retired_on,         :datetime

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :staffers do |t|
      t.column :name,               :string,   :default => "", :null => false
      t.column :person_id,          :integer # foreign key => people
      t.column :officiating_number, :string
      t.column :jersey_number,      :string
      t.column :picture_url,        :string
      t.column :thumb_url,          :string
      t.column :website_url,        :string

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :interleague_organization_staff_positions do |t|
      t.column :interleague_organization_id, :integer # foreign key => teams
      t.column :staffer_id,         :integer # foreign key => skaters
      t.column :staffer_type,       :string  # e.g. referee,coach,mascot,administration,scorekeeper,volunteer,other
      t.column :title,              :string
      t.column :started_on,         :datetime
      t.column :retired_on,         :datetime

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :league_staff_positions do |t|
      t.column :league_id,          :integer # foreign key => teams
      t.column :staffer_id,         :integer # foreign key => skaters
      t.column :staffer_type,       :string  # e.g. referee,coach,mascot,administration,scorekeeper,volunteer,other
      t.column :title,              :string
      t.column :started_on,         :datetime
      t.column :retired_on,         :datetime

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :team_staff_positions do |t|
      t.column :team_id,            :integer # foreign key => teams
      t.column :staffer_id,         :integer # foreign key => skaters
      t.column :staffer_type,       :string  # e.g. captain,coach,mascot,administration,other
      t.column :title,              :string
      t.column :started_on,         :datetime
      t.column :retired_on,         :datetime

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    #-----------------------------------

    create_table :bouts do |t|
      t.column :title,              :string
      t.column :start,              :datetime
      t.column :end,                :datetime
              # bout_type => enum ('bout','tournament')  # is_tournament?
      t.column :bout_type,          :string,   :default => "bout", :null => false
      t.column :website_url,        :string

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :games do |t|
      t.column :game_number,        :integer
      t.column :bout_id,            :integer # foreign key => bouts
      t.column :start,              :datetime
      t.column :end,                :datetime
      t.column :number_of_periods,  :integer
      t.column :length_of_periods,  :integer # Length in minutes
      t.column :website_url,        :string

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :periods do |t|
      t.column :period_number,      :integer
      t.column :start,              :datetime
      t.column :end,                :datetime
      t.column :game_id,            :integer # foreign key => games

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :jams do |t|
      t.column :jam_number,         :integer
      t.column :period_id,          :integer # foreign key => periods
      t.column :clock_start,        :string,   :limit => 5, :default => "00:00", :null => false # period clock
      t.column :clock_end,          :string,   :limit => 5, :default => "00:00", :null => false # period clock
      t.column :start,              :datetime
      t.column :end,                :datetime
              # called_off_by => enum ('clock','jammer','referee')
      t.column :called_off_by,      :string,                :default => "clock", :null => false
      t.column :duration,           :integer
      t.column :is_overtime,        :string,   :limit => 1, :default => "N",     :null => false

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :passes do |t|
      t.column :pass_number,        :integer # 0 = initial pass, >0 = scoring pass
      t.column :jam_id,             :integer # foreign key => jams
      t.column :skater_jam_id,      :integer # foreign key => skater_jams
      t.column :points,             :integer,               :default => 0
      t.column :is_lead,            :string,   :limit => 1 # enum('Y','N' [NULL => Incomplete])

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    #-----------------------------------

    create_table :game_teams do |t|
      t.column :game_id,            :integer # foreign key => games
      t.column :team_id,            :integer # foreign key => teams
      t.column :is_home_team,       :string,   :limit => 1, :default => "Y", :null => false
      t.column :position,           :integer

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :game_skaters do |t|
    # Applications should add all "game_team" members automatically, but this table allows for
    # other skaters filling in or players forming ad hoc teams 
      t.column :game_id,            :integer # foreign key => games
      t.column :skater_id,          :integer # foreign key => skaters
      t.column :is_home_team,       :string,   :limit => 1, :default => "Y", :null => false
      t.column :is_captain,         :string,   :limit => 1, :default => "N", :null => false # Sometimes need to designate captain on the fly
      t.column :is_active,          :string,   :limit => 1, :default => "Y", :null => false # 'N' for injury or absent team members

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :skater_jams do |t|
      t.column :jam_id,             :integer # foreign key => jams
      t.column :game_skater_id,     :integer # foreign key => game_skaters
      t.column :position,           :string  # enum('jammer','pivot','blocker','captain')

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    #-----------------------------------

    create_table :penalties do |t|
      t.column :name,               :string,                :default => "", :null => false
      t.column :code,               :string,   :limit => 2, :default => "", :null => false
      t.column :picture_url,        :string
      t.column :thumb_url,          :string

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :skater_jam_penalties do |t|
      t.column :penalty_number,     :integer
      t.column :skater_jam_id,      :integer # foreign key => skater_jams
      t.column :penalty_id,         :integer # foreign key => penalties
      t.column :is_major,           :string,   :limit => 1, :default => "N",     :null => false
      t.column :jam_clock,          :string,   :limit => 5, :default => "00:00", :null => false

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :skater_penalty_box_sessions do |t|
      t.column :session_number,     :integer
      t.column :game_skater_id,     :integer # foreign key => game_skaters
      t.column :clock_start,        :string,   :limit => 5, :default => "00:00", :null => false # period clock

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    #-----------------------------------

    create_table :locations do |t|
      t.column :street_address,     :string
      t.column :city_id,            :integer # foreign key => cities
      t.column :zipcode,            :string

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :cities do |t|
      t.column :name,               :string,   :default => "", :null => false
      t.column :abbreviation,       :string,   :limit => 10
      t.column :state_id,           :integer # foreign key => states

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :states do |t|
      t.column :name,               :string,   :default => "",      :null => false
      t.column :abbreviation,       :string,   :limit => 10
      t.column :country_id,         :integer # foreign key => countries

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    create_table :countries do |t|
      t.column :name,               :string,   :default => "",      :null => false
      t.column :abbreviation,       :string,   :limit => 10
              # state_type => enum ('state','province'[,'other']?)
      t.column :state_type,         :string,   :default => "state", :null => false # or province,principality,etc

      t.column :note_id,            :integer # foreign key => notes
      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end

    #-----------------------------------

    create_table :notes do |t|
      t.column :short,              :string
      t.column :long,               :text

      t.column :created_at,         :datetime
      t.column :updated_at,         :datetime
      t.column :lock_version,       :integer, :default => 0
    end
    #-----------------------------------
  end

  def self.down
#-----------------------------------
    drop_table :interleague_organizations
    drop_table :interleague_memberships
    drop_table :leagues
    drop_table :teams
#-----------------------------------
    drop_table :skaters
    drop_table :skater_teams
    drop_table :team_captains 
    drop_table :staffers
    drop_table :interleague_organization_staff_positions
    drop_table :league_staff_positions
    drop_table :team_staff_positions
#-----------------------------------
    drop_table :bouts
    drop_table :games
    drop_table :periods
    drop_table :jams
    drop_table :passes
    drop_table :game_teams
    drop_table :game_skaters
    drop_table :skater_jams
#-----------------------------------
    drop_table :penalties
    drop_table :skater_jam_penalties
    drop_table :skater_penalty_box_sessions 
#-----------------------------------
    drop_table :locations
    drop_table :cities
    drop_table :states
    drop_table :countries
#-----------------------------------
    drop_table :notes
#-----------------------------------
  end

end

